Example for Parallel Query 1

[longs]

Hi I am selecting from one table which have more than 18M rows and joining it with table B which has about 1M rows.

Query is like

select a.row1,
       a.row2,
       a.row3,
       B.some_row,
       a.row4
from   
       first_table a,
       sce_table   b
where  
       a.key = b.key;

This select take allot of time how can I do parallel query on this?

Thanks for your help

 

[SantaMufasa]

Longs[/I],

First, your labels in your sample query, above, worry me..."row1, row2, row3, some_row, and row4"...do you really mean, "col1, col2, col3, some_col, and col4"?

Secondly, can you confirm that there are at least two indexes: one for "first_table.key" and another for "sce_table.key"?...And whether or not those indexes are "UNIQUE INDEXes"?

Third, can you confirm the dates that statistics were last gathered on "first_table" and "sce_table"?

Once you have answered the questions, above, then we can begin to look at the use of parallel query.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[longs]

HI Mufasa

You are right that was a mistake it should be col1, col2 ...

for your questions 'first_table.key' and 'sce_table.key' both have indexes along with bunch of other indexes. They both are non-unique indexes.

I am not sure about the date when the statistics were gathered. Do we need to know that before we do parallel query?

Regards,
longbottom

 

[SantaMufasa]

Do we need to know (when the statistics were gathered) before we do parallel query?

Actually, even when using the parallel query capability, if your statistics are stale, performace can be terrible.

To determine when your statistics were last gathered, issue this query:

select table_name,last_analyzed
  from user_tables
 where table_name in ('FIRST_TABLE','SCE_TABLE');

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[longs]

'FIRST_TABLE' is 08/04/2008 07:00:46
and
'SCE_TABLE' is 08/11/2008 02:10:26

 

[SantaMufasa]

Okay, now that you have confirmed proper indexing and statistics, you can proceed to investigating the use of the parallel-query feature. There are certain restrictions and limitations. You can read up on those and see an example at Using Oracle's Parallel Execution Features, for example, and many, many other places you can find simply by Googling for "Oracle Parallel Query".

Let us know how your performance compares between the current execution time and a that of a parallelised query.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”