StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As usual for this type of question, an explain plan would be useful. It would help us work out what is actually going on in the database. </p> <p>Ideally you want to make sure the query is running on the remote database the sending the result set back, rather than sending the data across the link and running the query locally. This ensures that less data is sent across the link. The <code>DRIVING_SITE</code> hint can help with this, although Oracle is usually fairly smart about it so it might not help at all. </p> <p>Oracle seems to have got better at running remote queries but there still can be problems. </p> <p>Also, it might pay to simplify some of your date conversions. </p> <p>For example, replace this:</p> <pre><code>TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')- 1,'MM'),'MONYYYY') </code></pre> <p>with this:</p> <pre><code>TO_CHAR(add_months(TRUNC(SYSDATE,'MM'), -1),'MONYYYY') </code></pre> <p>It is probably slightly more efficient but also is easier to read.</p> <p>Likewise replace this:</p> <pre><code>WHERE DT >=TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')-1,'MM'),'YYYYMMDD')) AND DT < TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM'),'MM'),'YYYYMMDD')) </code></pre> <p>with </p> <pre><code>WHERE DT >=TO_NUMBER(TO_CHAR(add_months(TRUNC(SYSDATE,'MM'), -1),'YYYYMMDD')) AND DT < TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD')) </code></pre> <p>or even</p> <pre><code>WHERE DT >=TO_NUMBER(TO_CHAR(add_months(SYSDATE,-1),'YYYYMM"01"')) AND DT < TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMM"01"')) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload