1

Topic: To construct view on a star

Thoughts aloud.
The show-window storing in numerical indexes some id - links to reference manuals is constructed. The show-window - big, now the order of 12 million records, after implementation will quickly grow. The customer initially asked a show-window with text boxes, my initiative to store and create in the form of a star. Reference manuals small, from 1000 records to 30 000.
Decided to create view - a sheaf. I play with  and I try to kill some hares.
1) It would be desirable, that first two records returned quickly.
2) If a show-window will use for the full scanning and count of aggregates, it would be desirable that speed did not pump up.
Normal  first_rows solves the first task and does access to all tables through nested loops. At first the big table, and for each read line nested loop under remaining tables is read. Somewhere there are indexes, somewhere are not present. Accordingly,  tries to use them. It will seems to me for the full scanning such access long.
That was to achieve, but it does not turn out in any way. The server reads all reference manuals in storage, creating a hash function on connected fields. Then reads  the big table and on a hash finds lines from all reference manuals. If tables two (big and small) - it turns out. More - in any way.
Impart experience. Whether it is real? And as you arrive in such standard situation.
And still. On the tool I Use PL/SQL developer - there are questions on scanning explain_plan operating request. It seems to me, the operating request not always goes under that plan which it beforehand shows. Whether there is a possibility to scan the plan of operating session.

2

Re: To construct view on a star

bhr;

bhr wrote:

And still. On the tool I Use PL/SQL developer - there are questions on scanning explain_plan operating request. It seems to me, the operating request not always goes under that plan which it beforehand shows. Whether there is a possibility to scan the plan of operating session.

select * from table (dbms_xplan.display_cursor (' sql_id '))
SELECT DBMS_SQLTUNE.report_sql_monitor (
sql_id => ' 78xw3h8td4wuk ';
type => ' HTML ';
report_level => ' ALL ') AS report
FROM dual;

3

Re: To construct view on a star

bhr;
To begin with the analysis of predicates, reviewing of variants , indexings, .

4

Re: To construct view on a star

bhr;
How the given show-windows are often updated? Basis OLAP or OLTP? Probably it makes sense to do

5

Re: To construct view on a star

AmKad wrote:

bhr;
To begin with the analysis of predicates, reviewing of variants , indexings, .

I likely incorrectly described a problem. Or readers did not assume that such illogical task is put.
Where and as it will be used  - it is not known. The main show-window  indexes partitions. They are necessary or not - it will be known only when there will be specific targets on it. For me the customer - a black box. Asked a show-window, I made. That it with it will do - yet I do not know. Takes probably, simply away all data in  and will be there . Nevertheless. So far as I instead of a show-window give to it , it would be desirable that access to it was "fast". Here that also arises a question what to consider fast. Simply  from it that the first lines to look. Or  all table to preempt ....
Basically, while formulated , the answer ripened.  to it with  , let understands a data structure and if it is necessary to make summation of all with grouping, let uses the initial table or addresses to me.

6

Re: To construct view on a star

feagor wrote:

bhr;
How the given show-windows are often updated? Basis OLAP or OLTP? Probably it makes sense to do

When all will be tested, a show-window will be incremental  some times in day.
While all is filled at once -  and filling from zero.
I will probably create partitions on dates (on months or days).
? 5 fields varchar2 (256) - 100 million records for a year (roughly).

7

Re: To construct view on a star

bhr wrote:

Where and as it will be used  - it is not known. The main show-window  indexes partitions. They are necessary or not - it will be known only when there will be specific targets on it. For me the customer - a black box. Asked a show-window, I made. That it with it will do - yet I do not know.

It is not necessary to do today that it is necessary to alter tomorrow.

8

Re: To construct view on a star

AmKad;
I think, you are right

9

Re: To construct view on a star

bhr wrote:

they Are necessary or not - it will be known only when there will be specific targets on it.

That Kajt in the book wrote that the worst that you can make, it to test application under production conditions.
If you do not know indexes or not then badly analyzed the task are necessary. Means costs question not how to write  with  and how correctly to design basis.

10

Re: To construct view on a star

IMNO wrote:

it is passed...
That Kajt in the book wrote that the worst that you can make, it to test application under production conditions.
If you do not know indexes or not then badly analyzed the task are necessary. Means costs question not how to write  with  and how correctly to design basis.

To test application under production conditions
In dwh life is measured and slow. Users of objects - one and a half piece in day. Anything terrible does not happen.
Then badly analyzed the task
The world generally is incomplete, I reconciled to it. I here work practically nothing. Also began with calculation which some hours were considered. Finished about several minutes. Basis designing was not included into my duties. To me nobody put the finite task. Probably customers do not know her, for data analysis - process creative. Their business logic interests, instead of structurization and data storage.
And their word last - we under them are set up, instead of they under us.
And so yes. It is a lot of errors. And global - not wrong designing of basis, and is possible a birth not during that epoch, not in that country and not in that status.

11

Re: To construct view on a star

http://www.oracle.com/ocom/groups/publi … 026812.pdf

12

Re: To construct view on a star

wrote:

, dbms_xplan - it is possible to look at the plan of the last fulfilled request, well and  possibilities are

Thanks, tried. Moreover. Found it in pl_sql developer. In a window of sessions it is possible to look specific operating session and in one of the lower bookmarks (sql monitoring) - the operating plan. Any all , unusual. Thanks.

13

Re: To construct view on a star

bhr wrote:

thanks, tried. Moreover. Found it in pl_sql developer. In a window of sessions it is possible to look specific operating session and in one of the lower bookmarks (sql monitoring) - the operating plan. Any all , unusual. Thanks.

SQL Monitoring is the plan not recommended above dbms_xplan, it dbms_sqltune.report_sql_monitor.
At you after all Oracle Tuning Pack option in which frames it is licensed, yes is acquired?
If in window Sessions PL/SQL Developer push a wrench icon (in the upper part, near to the drop down list) get to dialogue where it is possible to adjust filters (and to a heap - to replace, for example, all calls to v $ on gv $ that is convenient if RAC). And on  Details this dialogue - just there will be a possibility to look as is formed  to the list of sessions, to correct an existing window and to add the - for example, with a call dbms_xplan smile.