Hot answers tagged oracle-10g-r2
13
You can do this with a normal view, as long as the users involved haven't already got access to the base table.
EG:
SQL> create user reportuser identified by reportuser;
User created.
SQL> grant create session to reportuser;
Grant succeeded.
SQL> grant create synonym to reportuser;
Grant succeeded.
SQL> select user from dual;
USER
...
4
with data as (
select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9
)
select
foo,
bar,
prev_foo
from
data
model
dimension by (foo, bar)
measures (cast (null as number) as prev_foo,
cast (null as number) as store_foo
)
rules (
store_foo[any, ...
4
I guess that using subqueries inside functions is the main performance problem.
It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only:
SELECT
loan.loan_number,
loan.borrower_name,
...
CASE WHEN loan.date_waiver_ordered IS NULL
THEN 'Not Ordered'
WHEN ...
3
It depends on the configuration you have in place.
Oracle uses log shipping (as default) to feed the transactions into the standby database. In order to make sure that ALL transactions are recovered on the standby database, we should make sure that the primary database is running in FORCE LOGGING mode. Switching FORCE LOGGING may cause a difference in ...
3
Here is one way, not using analytics so pretty inefficient:
with w as (select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9)
select foo, bar, (select max(foo) from w where foo<ww.foo and bar>ww.bar) from w ww;
1
The reason the task was hanging was that I had run a separate update statement against MY_TABLE in a SQL Developer client window, and had not completed the transaction. Because the transaction was still open, the Execute SQL Task could not complete. Committing the transaction in SQL Developer allowed the SSIS package to complete, however, it still generated ...
1
Here is another answer that works for nine rows, but not for more. I'm posting it in case it sparks an idea.
WITH w AS (SELECT level as foo, mod(ora_hash(level),4) as bar FROM dual CONNECT BY Level<9)
SELECT foo, bar, Case When Highest > Bar Then
MAX(foo) OVER (PARTITION BY Highest
ORDER BY foo RANGE BETWEEN UNBOUNDED PRECEDING AND 1 ...
1
Untested:
with W as
(select level as foo, mod(ora_hash(level),4) as bar
from dual
connect by level<9)
select foo, bar,
case when prev_bar > bar then prev_foo else null end as prev_bar
from (select foo, bar,
lag(foo) over (order by foo) as prev_foo,
lag(bar) over (order by foo) as prev_bar
from ...
Only top voted, non community-wiki answers of a minimum length are eligible
