SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Couple of questions on FTS and Parallel Hints

sgudipudiDec 16 2010 — edited Dec 18 2010

Hi Mates,

My oracle Version is -Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

select count(*)  from TAB_A
--------------------
 1921514 


select count(*) from TAB_A where cid=1234
-------------------
1859471 


select count(*) from TAB_A where cid=1234 and c_attr=4
-----------------
6601

Now i am inner joining TAB_A with TAB_B with the Following columns

A.cid=B.cid and A.rid=B.rid and A.kid=B.kid and A.pid=B.pid and A.c_attr=4

TAB_A doesn't have indexes on joining columns but TAB_B hasa composite index on pid and c_attr.

Now my questions are

   1.It is using index for all the other cid's except 1234.Only for the CID=1234 it is doing FTS(Full Table Scan) on TAB_A.
    
    I am wonderig how can i avoid the FTS on TAB_A with out creating any indexes on TAB_A(because its really a big table and have some composite indexes on 
    other coumns).

     2.How much % of data on a table would be better for index scan? I am assuming if data exceeds 15% then FTS is Good(my assumption only)

    3.Is there any disadvantages or downfalls if i use parallel hint to on those queries? because Parallel Hint may speed up the FTS.
    
    
    Is there any performance issue using Parallel hints? 
    
    If this post needs lack of information.Please let me know.

Thanks and Regards,
Mike

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 15 2011

Added on Dec 16 2010

5 comments

338 views