SQL Query Takes 15 to 20 sec

Question

The Query Explain Plan Link

The below Query is taking 15 to 20 Sec which is not expected. Please help in refactoring this query

SELECT upin.bipuserid userid,
       upin.orgn_entity_id entityId,
       COUNT(upin.ORGN_ENTITY_ID)
           over (partition BY upin.bipuserid) as numentities,
       COUNT(DISTINCT(SUBSTR(upin.ORGN_ENTITY_ID,6,2)))
           over (partition BY upin.bipuserid) as numentitytypes
FROM userpermission upin;

There are 1614246 Records in the table. Index has been created on both columns.

Below is the table structure

Column Name        Data Type    

BIPUSERID         VARCHAR2 (20 Char)            
ROLECODE          VARCHAR2 (25 Char)            
ORGN_ENTITY_ID    VARCHAR2 (10 Char)            
ACT_CD            VARCHAR2 (1 Char)

Could you post performance of the query without `numentitytypes` column? — Radim Bača, Jul 11 '18 at 11:41
Please [edit your question](https://stackoverflow.com/posts/51284558/edit) to include [the execution plan](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/generating-and-displaying-execution-plans.html) for the query (as text, not an image). — Alex Poole, Jul 11 '18 at 11:43

score 1 · Answer 1 · answered Jul 11 '18 at 11:44

1

How long does this query take?

select count(orgn_entity_id) as numentities ,
       count(distinct substr(upin.ORGN_ENTITY_ID, 6, 2) ) as numentitytypes
from userpermission upin
group by upin.bipuserid;

If this is significantly faster, then you can join these results back in. This may be able to take advantage of an index on userpermission(bipuserid, substr(upin.ORGN_ENTITY_ID, 6, 2), orgn_entity_id).

answered Jul 11 '18 at 11:44

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, This is significantly Faster But does not give me the required result for numentities and numentitytypes – Gagan Deep Jul 11 '18 at 17:00
@GaganDeep . . . If this doesn't give the right values, then the window functions won't either. This should have the right values, but just one per `bipuserid` row. – Gordon Linoff Jul 12 '18 at 02:01

score 0 · Answer 2 · answered Jul 11 '18 at 17:37

I believe that what @GordonLinoff was suggesting was that you should use his query as a basis for something like the following:

WITH cteCounts AS (SELECT BIPUSERID,
                          COUNT(ORGN_ENTITY_ID) AS NUMENTITIES ,
                          COUNT(DISTINCT SUBSTR(ORGN_ENTITY_ID, 6, 2) ) AS NUMENTITYTYPES
                     FROM USERPERMISSION
                     GROUP BY BIPUSERID)
SELECT c.BIPUSERID,
       up.ORGN_ENTITY_ID AS ENTITYID,
       c.NUMENTITIES,
       c.NUMENTITYTYPES
  FROM cteCounts c
  INNER JOIN USERPERMISSION up
    ON up.BIPUSERID = c.BIPUSERID

and add an index as follows:

CREATE INDEX USERPERMISSION_XXX(BIPUSERID, SUBSTR(ORGN_ENTITY_ID, 6, 2), ORGN_ENTITY_ID);

Create the index, then run the above query, and hopefully it'll work a bit quicker for you.

Best of luck.

SQL Query Takes 15 to 20 sec

2 Answers2