Explore MongoDB

Here we go,

http://mongly.com/tutorial/index

Bravo!

Hopefully you learnt something about MongoDB and Unicorns

There's a lot we didn't cover, like embedded documents, aggregation, indexes, management, etc

But the idea was to help get you confortable with the basics

Feel free to play around, insert new data, new combinations, and what not

Or, better yet, head over to the mongodb download page, grab the version for your OS and experiment on your local machine

The precompiled binaries available for Linux, Windows and OS X make MongoDB extremely easy to get started with

Don't forget to check out my free MongoDB ebook

You are now web scale

db.unicorns.find() 

_id	name	loves	dob	weight	gender	vampires
4dc9bbfbbc7a0515660052c8	Aurora	["carrot","grape"]	1991-01-25T13:14:00Z	450	f	43
4dc9bbfbbc7a0515660052cb	Solnara	["apple","carrot","chocolate"]	1985-07-05T02:01:00Z	550	f	80
4dc9bbfbbc7a0515660052cc	Ayna	["strawberry","lemon"]	1998-03-08T08:30:00Z	733	f	40
4dc9bbfbbc7a0515660052cf	Leia	["apple","watermelon"]	2001-10-09T14:53:00Z	601	f	33
4dc9bbfbbc7a0515660052d1	Nimue	["grape","carrot"]	1999-12-20T16:15:00Z	540	f	49
4dc9bbfbbc7a0515660052d3	Lois			55	f	0
4dc9bbfbbc7a0515660052c7	Horny	["carrot","papaya","orange"]	1992-03-14T07:47:00Z	600	m	63
4dc9bbfbbc7a0515660052c9	Unicrom	["energon","redbull","orange"]	1973-02-10T22:10:00Z	984	m	182
4dc9bbfbbc7a0515660052ca	Roooooodles	["apple","orange"]	1979-08-18T18:44:00Z	575	m	99
4dc9bbfbbc7a0515660052cd	Kenny	["grape","lemon","orange"]	1997-07-02T10:42:00Z	690	m	39
4dc9bbfbbc7a0515660052ce	Raleigh	["apple","sugar","orange"]	2005-05-04T00:57:00Z	421	m	2
4dc9bbfbbc7a0515660052d0	Pilot	["apple","watermelon","orange"]	1997-03-27T05:03:00Z	650	m	54
4dc9bbfbbc7a0515660052d2	Dunx	["grape","watermelon","orange"]	1976-07-19T19:18:00Z	704	m	165

What should the top five priorities be for new DBAs on the job?

Thought it's helpful to read again and again.

Be on fire. You have to want to learn everything, do everything, consume everything. So you got the DBA job, now is not the time to rest in your new chair and enjoy your success. List out your goals and what you want to learn. A five year plan is a great idea.


Listen! Ask Questions! Be involved! Don't just sit back waiting for the create table requests.


When it comes to theory, don't believe anything you hear or read until you have tried it yourself. Database rule number one, in my opinion, is that no rule of thumb applies all the time.


If you are solely responsible for a database, make darned sure, before you leave your job on day one, that your database can be recovered. Nothing else matters if you can't get that database back.


Document everything.


And, a baker's half-dozen...


Learn to use your voice of authority. You are the DBA, and this database is your responsibility. As you learn the right way, and the wrong way to do things (like, say, database design), you need to be an advocate for best practices and for good design. If you succeed, you can enjoy the rapture of success. If they don't listen, you will get the joy of "I told you so."


http://searchoracle.techtarget.com/news/1077095/So-you-want-to-be-a-DBA

effective index selectivity 2

File name...: A:\SQL\CBO\index_selectivity2.sql
Usage.......: @file_name
Description.: Why we need to avoid implicit data conversions on index columns in predicate.
Notes.......: index on VarChar2 columns
Parameters..:
Package.....: ._pkg

Modification History:

Date Who What
29-Oct-2010: Charlie(Yi): Create the file,

Goal
----
Show how implicit data conversions impact Effective Index Selectivity.
Prove that developers rely on implicit conversions is a bad practice.

Solution
--------
Create index on VarChar2 column(s), put number value in the predicate that use this index,
cause implicitly data type conversion.
E.g. TO_NUMBER(index_column) = n

Spec
----
cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

Logical Reads(LIO) of index access = BLevel + leaf_blocks * effective index selectivity

Buffers is LIO in function dbms_xplan.display_cursor output.

See Page 62(91) of Book: [Cost-Based Oracle Fundamentals]

Result
------
cost = 1 , LIO = 2 , No data conversion
cost = 26, LIO = 28, data conversion, from char to number

Data flow
---------

Test case
---------
* ,
* ,

Setup
-----
See below SQL code.

Reference
---
QA..: You can send feedbacks or questions about this script to charlie.zhu1 gmail.com
blog: http://mujiang.blogspot.com/

ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;

create table t
(
c1 VarChar2(5),
c2 VarChar2(7)
)
nologging;

create table t1
(
c1 varchar2(5),
n1 number(5)
)
nologging;

insert into t1(c1,n1) values('501',501);

insert /*+ append */ into t(c1,c2)
select mod(rownum,5), rownum
from dual
connect by level <=50000;

commit;

create unique index t_u1 on t(c1,c2) nologging;

exec dbms_stats.gather_table_stats(user,'t');
exec dbms_stats.gather_table_stats(user,'t1');

set serveroutput off
set linesize 200
ALTER SESSION SET STATISTICS_LEVEL=ALL;

REM -- use number value on VarChar2 index columns,

select * from t
where c1='1' and c2=(select n1 from t1);

SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, '+cost iostats memstats last partition'));

----------------------------------------------------------
| Id  | Operation          | Name | Cost (%CPU)| Buffers |
----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    29 (100)|      35 |
|*  1 |  INDEX RANGE SCAN  | T_U1 |    26   (0)|      35 |
|   2 |   TABLE ACCESS FULL| T1   |     3   (0)|       7 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"='1')
       filter(TO_NUMBER("C2")=)

REM -- use string value on VarChar2 index columns,

select * from t
where c1='1' and c2=(select c1 from t1);

SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'cost iostats memstats last partition'));

----------------------------------------------------------
| Id  | Operation          | Name | Cost (%CPU)| Buffers |
----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 (100)|       9 |
|*  1 |  INDEX UNIQUE SCAN | T_U1 |     1   (0)|       9 |
|   2 |   TABLE ACCESS FULL| T1   |     3   (0)|       7 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"='1' AND "C2"=)
.

Throttle big result set

Goal

To process a big query result set from database, many a time the application server is limited by memory footprint, so we need to throttle the output chunk by chunk.

Solution
Q: How do you eat an elephant? A: One piece at a time.
The staging table will put into a tablespace with EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M. you may choose other uniform size based on your result size limit.
And then we will read data one extent at a time, after finish one extent, mark it as processed.
When client application crashed or failed, we can restart at failed extent.

We may slow make the chunk across 2 or many extents to make it more flexible.

Reference
On the use of DBMS_ROWID.rowid_create
[http://bit.ly/bphHJb]

Setup

drop table rowid_range_job purge;

create table rowid_range_job
tablespace data_auto nologging
as
select e.extent_id, e.block_id, e.block_id+blocks-1 block_id_end,
 cast(dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) as urowid) min_rowid,
 cast(dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) as urowid) max_rowid,
 cast(o.object_name as varchar2(30)) table_name,
 cast(null as varchar2(30)) partition_name,
 cast('isbn_extract' as varchar2(30)) job_name,
 cast(null as date)         process_date,
 cast(null as number(1))    is_processed
from dba_extents e, user_objects o
 where o.object_name = 'T'
   and e.segment_name = 'T'
   and e.owner = user
   and e.segment_type = 'TABLE'
 order by e.extent_id
;

drop table t purge;
create table t
(
n1 number(10),
d1 date,
c1 varchar2(2000)
);

insert --+ append
 into t(n1,d1,c1)
select rownum, sysdate, rpad('a',1200)
from dual
connect by level <= 10000;
commit;

Generate and check rowid range split,

with data as
(
select e.extent_id, e.block_id, e.block_id+blocks-1,
       dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) min_rowid,
       dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) max_rowid
  from dba_extents e,
       user_objects o
 where o.object_name = 'T'
   and e.segment_name = o.object_name
   and e.owner = user
   and e.segment_type = 'TABLE'
)
select extent_id, count(*) cnt
  from data, T t
 where t.rowid between data.min_rowid and data.max_rowid
 group by rollup (extent_id)
;

delete rowid_range_job where job_name = 'test_output_job';

INSERT INTO rowid_range_job
   SELECT   e.extent_id, e.block_id, e.block_id + blocks - 1 block_id_end,
            CAST
               (DBMS_ROWID.rowid_create (1,
                                         o.data_object_id,
                                         e.file_id,
                                         e.block_id,
                                         0
                                        ) AS UROWID
               ) min_rowid,
            CAST
               (DBMS_ROWID.rowid_create (1,
                                         o.data_object_id,
                                         e.file_id,
                                         e.block_id + e.blocks - 1,
                                         32000
                                        ) AS UROWID
               ) max_rowid,
            CAST (o.object_name AS VARCHAR2 (30)) table_name,
            CAST (NULL AS VARCHAR2 (30)) partition_name,
            CAST ('test_output_job' AS VARCHAR2 (30)) job_name,
            CAST (NULL AS DATE) process_date, 0 is_processed
       FROM dba_extents e, user_objects o
      WHERE o.object_name LIKE 'T'
        AND e.segment_name = o.object_name
        AND e.owner = USER
        AND e.segment_type = 'TABLE'
   ORDER BY o.object_name, e.extent_id;

commit;

Query rowid extent range split metadata

select EXTENT_ID,
BLOCK_ID,
BLOCK_ID_END,
MIN_ROWID,
MAX_ROWID
--,TABLE_NAME
--,PARTITION_NAME
--,JOB_NAME
--,PROCESS_DATE
--,IS_PROCESSED 
from invdb.rowid_range_job where job_name = 'test_output_job';

with data as
(
  select extent_id, block_id, block_id_end, min_rowid, max_rowid, table_name, job_name, process_date, is_processed
  from rowid_range_job
  where job_name = 'test_output_job'
    and table_name = 'T'
)
select extent_id, count(*) cnt
  from data, T t
 where t.rowid between data.min_rowid and data.max_rowid
 group by rollup (extent_id)
/