nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to force OBIEE Reports to use table partitions instead of Full Table Scan

3010666

I have a Report in OBIEE which access a view table(The physical Layer) in Repository.

The View table has access to one of physical table which is partitioned based on DISCOM or group namely 'MG,UG,DG and PG'

But when the report runs instead of using table partition DISCOM wise it does a full table scan.

It actually should work like this suppose a customer logins to the application into a particular DISCOM say 'MG' with its MG userid say "MG7801" the Report should fetch data

only for that particlular DISCOM not for all. Similar for others.

How to reflect table partitioning in Report or repository???

Regards

Debayani

Find more posts tagged with

Accepted answers

All comments

AkanshAgarwal

Hi Debayani,

You can try by using the fragmentation properties of the LTS.

Regards,

Akansh Agarwal

3010666

Hi Akansh,

How to implement this fragmentation technique ? It will be helpful if you give few example.

Also as which partition of Table it will access whether MG /UG/DG/PG partition of table is dynamic

based on the User id of the customer. How can we implement the same?

Regards,

Debyani

3010666

Hi ,

This a sample query for your reference. The below query is in View table of Physical layer in Repositiory.

I have added table partitioning for DG group "P1_DGVCL" but how to do that dynamically for other groups based on User login Id?

SELECT COUNT(*) PAID_PENDING, 0 PAID_PENDING_RE, 0 NON_SURVEYED, 0 UNPAID_SURVEYED,

ORG_ID, CONS_CATEGORY_ID, MONTH_ID, SR_TASK_ID, REGION_ID, SERVICE_TYPE_ID, SCHEME_ID, OSR.ACTIVE_YN ACTIVE_YN

FROM RAPDRP_MIS_ODS.ODS_SR_TASK_STATUS PARTITION ("P1_DGVCL") OSR

JOIN RAPDRP_MIS_ODS.ODS_CLASSIFICATION C ON OSR.SR_STATUS = C.CODE

WHERE SR_TASK_NAME IN ('Firm Quotation','Estimate Payment')

AND C.CLASSIFICATION_TYPE = 'SR_STATUS'

AND C.ATTRB1 = 'OPEN'

AND OSR.SR_TASK_STATUS IN ('CLOSED','CLOSE')

AND OSR.SR_RECORD_ID IN(SELECT SR_RECORD_ID FROM RAPDRP_MIS_ODS.ODS_SR_TASK_STATUS

WHERE SR_RECORD_ID= OSR.SR_RECORD_ID AND SR_TASK_NAME IN ('Release Connection') AND SR_TASK_STATUS NOT IN ('CLOSE', 'CLOSED'))

GROUP BY 0, 0, 0, ORG_ID, CONS_CATEGORY_ID, MONTH_ID, SR_TASK_ID, REGION_ID, SERVICE_TYPE_ID, SCHEME_ID, OSR.ACTIVE_YN;

Regards

Debyani

AkanshAgarwal

Hi,

The way to implement this will to create alias of the tables based on the filter criteria.

Create a logical table with the all the alias as Multiple LTS.

And then fragment the tables based on the where condition of each table.

A session variable can be used to select the fragment runtime i.e the fragment can be based on the session variable.

I hope this helps.

Regards,

Akansh

rmoff

What you're doing here is fundamentally wrong. There's no nicer way to put it. The point of OBIEE is that it generates dynamic SQL based on the user requests, and if you model your RPD correctly in line with your physical data model (partitioning etc) then the physical queries generated will be correct such that they take advantage of partition pruning etc.

By putting a bunch of stuff in an inline view you're masking from OBIEE the true relationships within the data, so it's not surprising the generated SQL isn't what you were hoping for.

Model your physical tables as individual objects, set up the joins correctly, and then if your user builds a request with a given filter on one of your partitioned columns and you have built the RPD correctly, the generated physical SQL will include the necessary predicate to trigger partition pruning.

3010666

Hi,

The concern is RDP is already designed (including physical tables, logical tables..etc) and it is not using the functionality of table partitioning.

And now i have to implement this without redesigning the RPD from scratch.

Can i use partition table selection dynamically using the session variables.??

Regards

Debyani

rmoff

Technical debt. You're about to start accruing it in spades. Even more so if you try to "use partition table selection dynamically using the session variables".

[Deleted User]

3010666 wrote:

And now i have to implement this without redesigning the RPD from scratch.

To drive home Robin's point: That's basically making a bad thing worse. If you build your house on sand and things start falling apart it's pointless to repaint the walls.

Thomas Dodds

Use of information/data drives your design ... you HAVE a design that doesn't line up with your USE ... here's another vote to stop and assess and align your design to the required use.

Joel Acha

I Think all that needs to be said about this has already been said. The BI Server is only as clever as the repository model in a nutshell. If properly modelled, all underlying database performance indexes, partitions, hints etc. will be utilised in query generated by BI Server.

You'll have mo other option but to redesign your repository or this will be the start of many issues for you and your users.