Optimising a Select Query That Runs Slow on Oracle Which Runs Quickly on SQL Server

Optimising a SELECT query that runs slow on Oracle which runs quickly on SQL Server

Often this type of problem goes away if you analyze the tables involved (so Oracle has a better idea of the distribution of the data)

ANALYZE TABLE tasks COMPUTE STATISTICS;

Extremely slow Postgres query that runs fast in Oracle

Could you try this variant:

SELECT COUNT(DISTINCT P.product_id)
FROM product P
INNER JOIN product_cost_view PC
    ON P.product_id = PC.product_id
    AND P.user_id = PC.user_id
    AND P.product_type_id = PC.product_type_id
WHERE P.user_id = 1000000
    AND PC.cost_type = 'X'

Improve oracle query performance without indexing

First I'd rewrite the query to be ANSI standard:

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a
INNER JOIN itempages b ON b.ItemNum = a.ItemNum
INNER JOIN keygroupdata c ON c.ItemNum = b.ItemNum
WHERE a.ItemType IN (112,115,189,241)
ORDER BY a.DateStored DESC

This makes it easier to read and understand what is going on. It also helps you not make mistakes (i.e. Cross Joining)that might cause real big problems. Then I'd get the Explain plan to see what the DBMS is doing with that query. Is it trying to use some indexes? Is it joining the tables correctly?

Then I'd review the tables that I'm working with to see if there are any indexes that already exist that I could be using to make my query faster. Finally as everyone else has suggested I'd remove the Order By clause and just do that in code.

Very slow performing SQL case-expression (query optimization)

You should use LEFT JOIN instead of IN. If you have correct indexes on the columns disc_memberlist.user_id and billing_disc.resource_id it should improve performances as it would not do a full scan in your table.

SELECT 
    dm.user_id
    ,dm.agreement_id
    ,dm.cust_id
    ,dm.offer_id
    ,dm.offer_type
    ,dm.resource_spec_id
    ,dm.memberlist_subscriber_count
    ,NVL2(bd.resource_id, 'Y', 'N') AS Exists_in_billing
FROM disc_memberlist dm
LEFT JOIN billing_disc bd ON dm.user_id = bd.resource_id;

How to Increase SQL Query Performance on where clauses searching concatenated values

When you concatenate the string you are preventing the SQL optimizer to use the existing index on MainTable (USERNAME). That forces the engine to follow a different [slower] path; probably a HEAP [TABLE] SCAN. As simple as that.

If you really need to provide the full email address I would compute the concatenation in the last step and not before, essentially going back to your first option. For example:

Select USERNAME || '@domain.com', RCD From
(
    With Exmp1 AS 
    (
    Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
    FROM Table1 b 
    Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
    )

    Select USERNAME, RCD
    From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 

)
Where USERNAME = 'test1'

EDIT:

Taking the idea one step further you can rephrase the whole query and find out which optimizations are easily visible once the query is simplified:

• The thing is the column MT.USERNAME is probably much more selective than a.STATUS, so you should filter by it first.
• Then, to make the correlated subquery fast, you probably want to use a "covering index" on it, so I suggest adding ix2 as shown below.

For example:

Select
  MT.USERNAME || '@domain.com', a.RCD 
From MainTable MT
join Table1 a on a.ID = MT.ID
where MT.USERNAME = 'test1'
  and a.status = 'A'
  and a.EFFDT = (
    Select Max(b.EFFDT) FROM Table1 b Where a.ID = b.ID and a.RCD = b.RCD
  )

Now, in order for this query to be real fast you'll need the following indexes. It seems you already have the first one:

create index ix1 on MainTable (USERNAME); -- You already have this one
create index ix2 on Table1 (ID, RCD, EFFDT);

SECOND EDIT: If you really want to search using the full username you can add an index on an expression. Take your "Example 2" and change the WHERE condition as shown below:

Select * From
(
    With Exmp1 AS 
    (
    Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
    FROM Table1 b 
    Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
    )

    Select USERNAME || '@domain.com', RCD
    From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 

)
Where USERNAME || '@domain.com' = 'test1@domain.com' -- changed here

Then add the following index:

create index ix3 on MainTable (USERNAME || '@domain.com');

This should make the query fast, since the filtering preficate will be an exact match with the index.

---

Related Topics