//AMIS Technology Blog » Oracle wizard » Page 3

Posts tagged Oracle wizard

SIG Event

Oracle 11G: XMLQuery = eval

5 years ago
by Anton Scheffer in , , ,

A nice little trick on Oracle 11G is using XMLQuery as an eval function:

SQL>l   1  select substr( sys_connect_by_path( level, '*' ), 2 ) || ' = ' ||   2         XMLQuery( substr( sys_connect_by_path( level, '*' ), 2 ) RETURNING CONTENT).getnumberval() product   3       , substr( sys_connect_by_path( level, '+' ), 2 ) || ' = ' ||   4         XMLQuery( substr( sys_connect_by_path( level, '+' ), 2 ) RETURNING CONTENT).getnumberval() sum   5  from dual   6* connect by level < 6 SQL>/ PRODUCT                   SUM ------------------------- ------------------------- 1 = 1                     1 = 1 1*2 = 2                   1+2 = 3 1*2*3 = 6                 1+2+3 = 6 1*2*3*4 = 24              1+2+3+4 = 10 1*2*3*4*5 = 120           1+2+3+4+5 = 15

Anton   Marco’s first performance  investigation:

== First Time ==

Execution Plan ———————————————————- Plan hash value: 1236776825

—————————————————————————– | Id | Operation | Name | Rows | Cost (%CPU)| Time | —————————————————————————– | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | |* 1 | CONNECT BY WITHOUT FILTERING| | | | | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | —————————————————————————–

Predicate Information More >

SIG Event

Solving a Sudoku with 1 SQL-statement: the Model-clause

5 years ago
by Anton Scheffer in , ,

Solving a Suduku with one SQL-statement, is that possible? A lot of people won’t believe it, but yes, it can be done. (more…)

SIG Event

Solving a Sudoku with Collections

6 years ago
by Anton Scheffer in , ,

If you want to write a program to solve sudokus, you can use almost any programming language. And PL/SQL too!

(more…)

SIG Event

More Jonathan Lewis

7 years ago
by Anton Scheffer in ,

A little while ago, Jonathan Lewis presented a three day seminar in The Netherlands. You may have read other blogs on this site about this seminar. AMIS had sent six people to attend. And yes, I was one of the lucky ones too. Although the seminar should be for developers and DBA’s, most of its content was aimed at DBA’s. As a developer I don’t think undo, redo and latches are very interesting. But there were enough nice things to make the seminar very useful. For instance

  • A nice example of how the way/order which records are inserted into a table can have impact on the performance of retrieving the data, due to differences in the clustering factor of the data/indexes.
  • An other example of querying simular datasets (in the same way), but with huge performance differences, if you are unlucky with some internal hash functions of Oracle.
  • A working example of the "go_faster" hint
  • A nice trick to get the DBA-role, see http://www.red-database-security.com/exploits/oracle_sql_injection_oracle_dbms_metadata.html
  • and much more

Anton

SIG Event

Anydata and Collections

8 years ago
by Anton Scheffer in , ,

Steven Feuerstein has written a PLSQL-utility str2list in which he uses dynamic sql to fill collections of different types. While I was playing with the “new” Oracle types ANYTYPE, ANYDATA and ANYDATSET I found that the ANYDATA type can be used to do something simular.

So I have written a function which accepts a string and a delimiter, and returns the string split in several parts in an ANYDATA which can be deposited in a collection of different types. (more…)

Go to Top