Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Wednesday, October 25, 2006

Search Oracle Blogs

Google announced release of new tool Google Custom Search Engine. Using this tool You can create our own list of sites you want to search within. There are also many options to customize search result pages, to have search results on your own site. You may also collaborate with other users, to create list of sites on specific topic.
Based o this I’ve created a new search engine that searches only Blogs related to Oracle. You can check if you are already listed. If not you can add your site. You are welcome to take part and propose sites with oracle related blogs. For sure I will include it in this listing.
On top of my page you can find search box. Try it.
If you want this search box on your page just copy following HTML code
<!-- Google CSE Search Box Begins -->
<form id="searchbox_007198821948994293043:dv284dbx17s" action="http://www.google.com/cse">
  <input type="hidden" name="cx" value="007198821948994293043:dv284dbx17s" />
  <input name="q" type="text" size="40" />
  <input type="submit" name="sa" value="Search" />
  <input type="hidden" name="cof" value="FORID:1" />
</form>
<script type="text/javascript" src="http://www.google.com/coop/cse/brand?form=searchbox_007198821948994293043%3Adv284dbx17s"></script>
<!-- Google CSE Search Box Ends -->


Paweł

Oracle 11g new features, part 2

There are two new interesting sources for 11g new features.
Tim Hall post The Next-Generation Self-Managing Database (11g)
And Lewis Cunningham post OOW: Enhancements in Oracle 11g PL/SQL
Also these features or enhancements seems very promising
  1. PL/SQL Native Compilation. There is no need for external C compiler, so it’s easier to make PL/SQL faster.
  2. New Data Type: simple_integer. Another performance booster.
  3. Intra-unit inlining. - this is feature that I was missing for years. Now I can create complex expressions and reuse it easily. I hope it will work also in SQL
  4. Database Replay and SQL Replay - this is very interesting idea.
I'm looking forward to play with these features.

Cheers, Paweł

Tuesday, October 24, 2006

Oracle 11g new features

I have found Laurent post on 11g new features very interesting. Let me comment on some items:
  1. /*+result_cache*/ is very promising. From description it seems that it might be very useful for web applications based on Oracle. But sentence "There is an aggressive cache invalidation, so whatever DML happens on one dependent table, the cache result is invalidated" suggest that it will be useful only in read only environment. Otherwise cache will be constantly invalidated making this feature useless. Question is if query results will be stored in new memory structure, or it will be in Buffer Cache? Anyway there might be need for huge RAM
  2. Using sequence in PL/SQL without DML. This is long time expected feature
  3. Trigger order. This can be also sometimes useful.
All other features seem to be just improvements or enhancements. I do not see any revolutionary solution in this list.

Paweł

Saturday, October 21, 2006

Binding list variable

Today I was working with XMLType, and as a side effect I found interesting solution to transform list of values contained in single string to rows. You can find some solutions on Tom Kyte blog. But my solution is new approach that uses xmlType and xmlSequence
SQL> var list varchar2(200)
SQL> exec :list := '2,4,6,8,10,34,33';

PL/SQL procedure successfully completed.

SQL> select items.extract('/l/text()').getStringVal() item
  2  from table(xmlSequence(
  3    extract(XMLType('<all><l>'||
  4      replace(:list,',','</l><l>')||'</l></all>')
  5          ,'/all/l'))) items;

ITEM                                                                            
--------------------------------------------------------------------------------
2                                                                               
4                                                                               
6                                                                               
8                                                                               
10                                                                              
34                                                                              
33                                                                              

7 rows selected.
Simple! Isn’t it?
Cheers, Paweł

Wednesday, October 18, 2006

What tool do you use to make blog backup?

Today a have a question to you: What tool do you use to make backup of your blog?
My blog in hosted on blogspot and is run by beta.blogger.com. I was trying to make backup (mirror) using HTTrack but it run into infinite loop. It was downloading every post many, many, many.... times with little changed name.
Looking forward for your recommendation…
Paweł

Monday, October 16, 2006

A better view? I do not think so.

I have read today Gojko Adzic article A better view. I must say that it made me thinking about my approach to database and front-end applications. I do not share his opinion.
In my approach PL/SQL API is the only possible way to retrieve and manipulate data in Oracle Database. Especialy when it goes for Web Applications.
  • Logging user activity. Using triggers is fantasting to logging changes to data (Journaling), but usually is useless for logging user activity. User activity should be logged based on his/her actions and it’s parameters not data modifications. You do not want your batch processes to generate so many log.
  • Performance Monitoring. With well-written API you can get very detailed statistics for time consumed by individual routine. Based on this statistics You can decide where are your bottlenecks and you can improve that.
  • Simplicity. For easy, standard operations You should prepare generators that automatically generate API procedures for many simple tables.
  • Cursors. PL/SQL procedures can return REF CURSORS as an output. With dynamic SQL you can return different variations of data depending on input parameters.
  • API can be used for many Applications. In many big organizations one database is often accessed by different client applications. You do not have to write the same (similar) SQL’s in PHP, java or whatever other tool You want/plan to use.
Hope You understand my point of view.
If you are disagree or agree, please leave a comment.

Cheers, Paweł

Sunday, October 15, 2006

Group Comparison Conditions

Sometimes there is a need to compare one value to many others. In Oracle SQL or PL/SQL for this purpose you can use Group Comparisons. Syntax for this is:
expr { = | != | < | > | <> | <= | >= } {ANY | SOME | ALL} (expr, …, expr) , for example X = ANY (1,2,3) .
You can use subquery instead of expression list. For full syntax go to Oracle Documentation. Lets see how it works. First create sample table:
SQL> CREATE TABLE T1 (N1 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T1 SELECT LEV, LEV
  2  FROM (SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL < 10);
9 rows created.
SQL> COMMIT;
Commit complete.
Then do some queries
SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
And lets see what will happen if there is NULL in list:
SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6, NULL);
no rows selected
So we can see that ANY is equivalent to SOME. These Grouping conditions are in fact rewritten to simple conditions. ANY is rewritten to many comparisons with logical OR, a < ANY (b,c,d) is rewritten to (a<b OR a<c OR a<d) while ALL is rewritten to AND statement a < ALL(b,c,d) becomes (a<b AND a<c AND a<d).
We can observe that = ANY is equivalent to IN operator.
SQL> SELECT * FROM T1 WHERE N1 = ANY (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         4 4                                                                    
         6 6                                                                    
SQL> SELECT * FROM T1 WHERE N1 IN (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         4 4                                                                    
         6 6                                                                    
Also observe that != ALL is equivalent to NOT IN operator.
SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         5 5                                                                    
         7 7                                                                    
         8 8                                                                    
         9 9                                                                    
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         5 5                                                                    
         7 7                                                                    
         8 8                                                                    
         9 9                                                                    
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6, NULL);
no rows selected
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6, NULL);
no rows selected
Take a look at trick with joining many tables based on the same key. Lets create more tables
SQL> CREATE TABLE T2 (N2 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T2 SELECT * FROM T1;
9 rows created.
SQL> CREATE TABLE T3 (N3 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T3 SELECT * FROM T1;
9 rows created.
SQL> COMMIT;
Commit complete.
And simple join:
SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
        N1         N2         N3                                                
---------- ---------- ----------                                                
         1          1          1                                                
         2          2          2                                                
         3          3          3                                                
         4          4          4                                                
         5          5          5                                                
         6          6          6                                                
         7          7          7                                                
         8          8          8                                                
         9          9          9                                                
9 rows selected.
Is it really evaluated in proper way?
SQL> set autotrace traceonly;
SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
9 rows selected.
Execution Plan
----------------------------------------------
Plan hash value: 3348756600 
---------------------------------------------- ...
| Id  | Operation              | Name        | ...
---------------------------------------------- ...                                   
|   0 | SELECT STATEMENT       |             | ...
|   1 |  NESTED LOOPS          |             | ...
|   2 |   NESTED LOOPS         |             | ...
|   3 |    INDEX FAST FULL SCAN| SYS_C004041 | ...
|*  4 |    INDEX UNIQUE SCAN   | SYS_C004042 | ...
|*  5 |   INDEX UNIQUE SCAN    | SYS_C004043 | ...
---------------------------------------------- ...                                         
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   4 - access("N1"="N2") 
   5 - access("N1"="N3") 
So we can observe that it is really rewritten and oracle uses indexes in optimal way.
Hope you find it useful

Paweł

GUI for FireBird DB

Friend of mine, that is still working on xBase (Clipper), asked me to recommend him a good GUI tool for SQL database. At first I didn't know what he exactly wanted. In fact he wanted a tool to retrive and manipulate data in FireBird DB. After trying few tools I recommended IB_SQL. It's realy light weighted and intuitive.

It was my first experience with FireBird. I must say that for me it seems insecure. Whole database is stored in one file, and if you have this file, you can easily open it without need for any password.

Paweł

Friday, October 06, 2006

RMAN connection to Catalog

Today my nightly backups were not taken. I had an OS script that started backup using this command:
rman target=sysop/password@targetdb catalog=rman/password@catdb 
      log=rman.log append @rman.txt
The reason was that network was down and there was no connection to catdb. It leaded to error and RMAN did not start at all. So I’ve changed my command line to:
rman target=sysop/password@targetdb 
      log=rman.log append @rman.txt
And added a line in rman.txt
connect catalog rman/password@catdb;
Next time there will be no connection to catdb backup will be done anyway. It will be not registered in catalog only. Catalog will be synchronized on next backup procedure or next time you connect to target and catalog an issue any command that operate on catalog.

Cheers, Paweł

Thursday, October 05, 2006

More on Flashback

If you are instrested in more materials on Oracle Flashback you should take a look at Tom Kyte presentation TomKyte_FlashBackQ_Mar-05.zip found on Calgary Oracle Users Group.
Polish speeking readers can have a look at my article PLOUG'tki nr 31 (IX 2004) - Podróż w czasie, czyli Oracle Flashback that I wrote for Polish Oracle Users Group.

Cheers, Paweł

Related articles:

Wednesday, October 04, 2006

UNDO_SQL in FLASHBACK_TRANSACTION_QUERY

When You read my post on Flashback and Materialized View You might think about other workaround. In FLASHBACK_TRANSACTION_QUERY View there is column with SQL Statement to Undo operation on table. Lets do simple test:
SQL> insert into a_fb values (1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> delete from a_fb;
1 row deleted.
SQL> commit;
And now see whats in FLASHBACK_TRANSACTION_QUERY.
SQL> select commit_scn, operation, row_id, undo_sql
  2  from FLASHBACK_TRANSACTION_QUERY
  3  where table_name = 'A_FB'
  4  order by commit_scn;
COMMIT_SCN OPERATION                        ROW_ID                              
---------- -------------------------------- -------------------                 
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
    641488 INSERT                           AAAM2dAAEAAAAG9AAA                  
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';                  
                                                                                
    641571 DELETE                           AAAM2dAAEAAAAG9AAA                  
insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1'); 
To run above query You need SELECT ANY TRANSACTION privilege
So now instead of issuing FLASHBACK TABLE A_FB to SCN 641400 You may run statements from UNDO_SQL:
insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1');
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';
But it will not work! Because when you insert new row usually it will get new row id
SQL> select rowid, id from a_fb;

ROWID                      ID                                                   
------------------ ----------                                                   
AAAM2dAAEAAAAG9AAB          1
So when issuing Delete statement you must map old rowid to that new one. When you do Flashback on Table oracle manages it internally. But when you use UNDO_SQL you must take care for all details. So be careful with that…

Cheers, Paweł

Related articles:

 

Copyright © Paweł Barut
Printing from DOS to USB Printer