Oracle/PL/SQL

Advertisement


Question
Hi Sir,

My Question are...

1)In what case hint is helping and in what case it is reducing the performance for a query?

2)Difference between CLOB & BLOB and can
we use CLOB for BLOB data and BLOB for CLOB data?


Thanks in advance

Gaurav

Answer
Good day Gaurav

1)In what case hint is helping and in what case it is reducing the performance for a query?
There are scenarios that Oracle optimizer did not create the optimal execution path to retrieve the data.  HINT allows you to overwrite Oracle execution path to improve the performance.  However, you will need to make sure to verify whether the HINT is still validate if
(1) Statistics change
(2) Cardinality change
(3) Index change
All these can have direct impact to the execution path, and it can have impact to your HINT performance.

2)Difference between CLOB & BLOB and can we use CLOB for BLOB data and BLOB for CLOB data?
Nothing stopping you to use CLOB for BLOB and BLOB for CLOB.  Unless there is specific reason why you need to do that, I do not recommend it.   That's precisely why Oracle has CLOB and BLOB datatype.
To insert CLOB into BLOB, you will need to use the dbms_lob.converttoblob function.  To insert BLOB to CLOB, you will need to use dbms_lob.converttoclob function.

Hope this help
Elliot

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Elliot Mak

Expertise

I can answer questions with regarding to Oracle DB (8i, 9i, 10g, 11g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).

Experience

I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.

Education/Credentials
Bachelor of Science in Management System

©2016 About.com. All rights reserved.