CODEWITHSUNDEEP
performanceoracle-databasejoincursor
Arun Vijay
Arun Vijay

Reputation: 121

Join in Cursor query or two cursors, which is faster?

I need some suggestions for my cursor which is expected to run against millions of records. Here is my cursor query.

CURSOR items_cursor IS       -- Brings only records that need to be updated
        SELECT a.*, b.* FROM  
        ( SELECT DataId, Name, VersionNum, OwnerId, SubType, LEVEL Lev FROM DTree 
          START WITH ParentId = startFrom CONNECT BY PRIOR DataId= ABS(ParentId)        -- Brings ABS of ParentId
        )a, 
        (                  
         SELECT o.DataId pDataId, o.Permissions OwnerPerm, p.Permissions PublicPerm FROM DTreeAcl o, DTreeAcl p WHERE
         o.DataId=p.Dataid AND o.AclType=1 AND p.AclType=3 AND (o.Permissions != ownerPerm OR p.Permissions != publicPerm)
        )b        
        WHERE a.Lev >= 1 AND a.Lev <= 3 AND a.DataId = b.pDataId;

Is it better to get data from second table in another cursor inside the first cursor than join everything in first cursor itself??

Upvotes: 0

Views: 1291

Answers (1)

Justin Cave
Justin Cave

Reputation: 231791

A database is built to join. In the vast majority of cases, you're better off letting the database do the join in SQL rather than trying to write your own in PL/SQL.

The only way you'd be better off writing the join in PL/SQL would be if you know that you want a nested loop join and the Oracle optimizer chooses a much less efficient plan. In that case, though, you'd be better off getting the optimizer to give you the plan you want rather than writing a nested loop join in PL/SQL.

Upvotes: 3

Related Questions