Re: Improving performance of queries

On Wed, 05 Apr 2006 19:14:15 +0200, Sybrand Bakker
<postbus@xxxxxxxxxxxxxxxxx> wrote:

On 5 Apr 2006 09:09:04 -0700, "News" <Contact_404@xxxxxxxxxxx> wrote:

Most of process time of ETL Jobs is due to simple minus queries against
partitioned tables like this

SELECT DISTINCT x
FROM A
MINUS
SELECT y
FROM B

SELECT STATEMENT
0 6475
MINUS
1 0 1
SORT UNIQUE
2 1 1
PARTITION RANGE ALL
3 2 1
INDEX FAST FULL SCAN A_PK1
4 3 1
SORT UNIQUE
5 1 2
INDEX FAST FULL SCAN B_PK
6 5 1

6475 SELECT STATEMENT Cost= 6475
1 MINUS
1 SORT UNIQUE
1 PARTITION RANGE ALL
1 INDEX FAST FULL SCAN A_PK1
2 SORT UNIQUE
1 INDEX FAST FULL SCAN B_PK

Such query takes 15 mn to run. There are lot of them. Is there a way to
rewrite such query or improve plan in 10gr2 under aix 5.2 ?


I don't think so. This is the most efficient plan for the given query.
The DISTINCT however is redundant, as MINUS is a SET operations and a
set can't have duplicates.
This will remove 1 sort unique from the plan.

What about parallel queries and looking into IO?
OP didn't state how big the tables are.

........
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email
.

Relevant Pages

  • Re: Improving performance of queries
    ... 6475 SELECT STATEMENT Cost= 6475 ... Such query takes 15 mn to run. ... This is the most efficient plan for the given query. ... as MINUS is a SET operations and a ...
    (comp.databases.oracle.server)
  • Re: Improving performance of queries
    ... 6475 SELECT STATEMENT Cost= 6475 ... Such query takes 15 mn to run. ... Is there a way to rewrite the query? ... Check the plan and performances of those versions. ...
    (comp.databases.oracle.server)
  • Re: Why is this
    ... would be to have a single index on (lastrecon, acctnum, deposit) [or ... cover the query and handle the restrictive condition immediately. ... there is no perfect query plan. ... use the index for TOP 1, even if it used quite a few bookmark lookups, ...
    (microsoft.public.sqlserver.server)
  • Re: Query optimizer issue
    ... Consider a stored proc defined as follows: ... when compiling a plan. ... parameter @p1 into the query at compile time before a plan for the query ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.server)
  • Re: Execute SQL UD Function call timeout, 1-3 seconds in query ana
    ... We did build a little ASP.NET page yesterday that lets us put a given sql ... query into a text box and then return the execution plan (using Showplan_Text ... Query Analyzer. ...
    (microsoft.public.dotnet.framework.adonet)
Loading