Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:
create table test(n not null) as
with gen as (select level n from dual connect by level<=100)
select g2.n as n
from gen g1, gen g2
create index ix_test on test(n)
And special thanks to all the great people who voted for me!
I want to list all voters for all nomenees in one page: http://orasql.org/odevchoice/all-the-voters.html
The query (using xt_http of course )
finalists(category, userid, name) as (
-- SQL~ Voting:
------ ------------------ -------------------------------------------------------------------------------------
select 'SQL' , 6899, 'Stew Ashton ' from dual union all
select 'SQL' , 6900, 'Sean Stuber ' from dual union all
select 'SQL' , 6901, 'Sayan Malakshinov' from (more...)
A couple days ago i created simple package for HTTPS/HTTP, but I’ve decided now to improve it:
- Timeout parameter – it would be better to control connection time;
- Simple page parsing with PCRE regular expressions – to speed up and simplify page parsing, because if you want to get big number matched expressions from CLOB with regexp_xxx oracle functions, you have to call these functions many times with different [occurance] parameters, passing/accessing to the (more...)
It’s very easy to get and analyze voters list using my new XT_HTTP package
We can get up-voters list by the URL:
where NNNN is Idea ID from nomenee’s page.
For example my page – https://community.oracle.com/ideas/6901 so my voters page will be https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000
BTW, though this page is called “VotingHistory”, but it shows up-voters only
So we can easily get full voters list:
I don’t like to import certificates, so i cannot use httpuritype for HTTPS pages and I decided to create package which will work with https as http.
It was pretty easy with java stored procedures
java source: xt_http.jsp
create or replace and compile java source named xt_http as
This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.
As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.
Test function and tables
create or replace function f_without_deps(p_tab varchar2) return varchar2
execute immediate 'select '''||p_tab||''' from '||p_tab||' where rownum=1' into res;
create table a as select 'a' a from dual;
create table b as select 'b' b from dual;
create view v_ab as select a,b from a,b;
Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
You know that if we want to execute another script from the current script directory, we can call it through @@, but sometimes we want to know the current path exactly, for example if we want to spool something into the file in the same directory.
Unfortunately we cannot use “spool @spoolfile”, but it is easy to find this path, because we know that SQL*Plus shows this path in the error when it can’t (more...)
How many times have you guessed the right answer?
select * from dual order by -1;
select * from dual order by 0;
select * from dual order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0;
select 1 n,0 n,2 (more...)