increase the performance on a view

[mkey]

hi all,
I wrote a view which has two table joins.
Now I'm just wondering how I could increase
the performance. I already have the a.tc and b.c indexed.
In addition the views are retriving 60% of the records.
So I'm not sure if in fact putting indexes will
increase the performance. Also I would like to
know what kind of view will be good in this regard.
B-tree or Bitmap?

The view looks something like this:

SELECT
A.SN AS S0,
'NA' AS S1,
A.ID,
A.SSID AS SS0,
A.DESCR AS DESCR1,
B.DESCR AS ALT_DESCR1,
'NA' AS DESCR2,
'NA' AS INSERT_SYMB,
'NA' AS AB
FROM
schema1.table_A A,
schema2.table_B B,
WHERE
A.ESC IN (10,20,30)
AND A.CI = 1
AND A.TC = B.C

AND A.TC IN
('ABC','BDE',DER',DRQ','OIUO','DQOP','QUIE','PIIU','EU28','DLWI')


thank you in advance!

 

[vgg]

Hi mkey,

1.Analyze the execution of the query using EXPLAIN PLAN and determine which access path and indexes are choosen by the oracle optimizer.(If the operation is FULL TABLE scan ,then it means your optimizer is not considering the indexes you created.just because you created indexes doesn't mean that the optimizer has to use them.

I would suggest you try to execute the query with the PARALLEL hint specified.you can gain considerable improvement in performance.

2.You can choose a bitmap index only if the column is a low cardinality(The no. of distinct values is less compared to the total no. of records).
A common example is a Person table with "sex" column.
Even if you have millions of records,the sex can be either 'M' or 'F'.So the distinct values are just 2.
Hence this column is an eligible candidate for a bitmap index.
Also when you choose to create a bitmap index make sure that your not going to add any new valuein the column.
Then your entire index need to be reorganized,which will definitely affect the performance.

B*tree is the structure in which oracle stores any normal index.



regards,
VGG

 

[mkey]

HI VGG,
How can I use the PARALLEL hint within my view?

Thanks again for all your help!

 

[vgg]

Hi ,

Here's an example:

SELECT /*+ PARALLEL(e,10,DEFAULT) */ ename,sal

FROM emp e;

Note:
1.The first argument to the PARALLEL hint specifies the alias to the table(is a must).
2.The second argument specifies the degree of parallelism.
Can also be DEFAULT.
3.The third argument specifies that the table should be shared by n no. of instances.Here it is shared between
the available instances.So you query is processed simultaneusly by different server processes and the cumulative result is returned.

regards,
VGG